﻿using System.Data.Linq;
using System.Linq;
using UniMana.Data;
using System;

namespace UniMana.Data
{
    public static class ReportStudent_StatusData_Chi
    {
        public static IQueryable Student_StatusData(this Table<Training_Student_Class> studentClas,
                                                         Table<Catalogue_Course> cataCou,
                                                         Table<Catalogue_Department> cataDep,
                                                         Table<Catalogue_Level> cataLe,
                                                         Table<Catalogue_Major>cataMaj,                                       
                                                         Table<Catalogue_Status> cataSta,
                                                         Table<Catalogue_TrainingType> cataTrai,
                                                         int _iCou, int _iDep, int _iMaj, int _iTrai, int _ilevel)//, int _iSta)
        {
            var view = from t in cataMaj
                       join t0 in cataDep on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t0.Catalogue_DepartmentKey }
                       join t1 in cataLe on new { Catalogue_LevelKey = Convert.ToInt32(t.Catalogue_LevelKey) } equals new { Catalogue_LevelKey = t1.Catalogue_LevelKey }
                       join t2 in studentClas
                             on new { t.Catalogue_MajorKey, t1.Catalogue_LevelKey }
                         equals new { t2.Catalogue_MajorKey, Catalogue_LevelKey = Convert.ToInt32(t2.Catalogue_LevelKey) }
                       join t3 in cataSta on new { Catalogue_StatusKey = Convert.ToInt32(t2.Catalogue_StatusKey) } equals new { Catalogue_StatusKey = t3.Catalogue_StatusKey }
                       join t4 in cataCou on new { Catalogue_CourseKey = Convert.ToInt32(t2.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t4.Catalogue_CourseKey }
                       join t5 in cataTrai on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t2.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t5.Catalogue_TrainingTypeKey }
                       group new { t4, t0, t, t1, t5, t3 } by new
                       {
                           t4.Catalogue_CourseKey,
                           t4.Name,
                           t0.Catalogue_DepartmentKey,
                           Column1 = t0.Name,
                           t.Catalogue_MajorKey,
                           Column2 = t.Name,
                           t1.Catalogue_LevelKey,
                           Column3 = t1.Name,
                           t5.Catalogue_TrainingTypeKey,
                           Column4 = t5.Name,
                           t3.Catalogue_StatusKey,
                           Column5 = t3.Name
                       } into g
                       orderby  g.Key.Catalogue_CourseKey
                       // where
                       ////where g.Key.Catalogue_StatusKey == _iSta &&
                       //  g.Key.Catalogue_CourseKey == _iCou &&
                       //  g.Key.Catalogue_DepartmentKey == _iDep &&
                       //  g.Key.Catalogue_MajorKey == _iMaj &&
                       //  g.Key.Catalogue_LevelKey == _ilevel &&
                       //  g.Key.Catalogue_TrainingTypeKey == _iTrai
                       select new
                       {
                           Catalogue_CourseKey = (System.Int32?)g.Key.Catalogue_CourseKey,
                           TenKhoaHoc = g.Key.Name,
                           Catalogue_DepartmentKey = (System.Int32?)g.Key.Catalogue_DepartmentKey,
                           TenKhoa = g.Key.Column1,
                           Catalogue_MajorKey = (System.Int32?)g.Key.Catalogue_MajorKey,
                           TenNganh = g.Key.Column2,
                           Catalogue_LevelKey = (System.Int32?)g.Key.Catalogue_LevelKey,
                           TenBDT = g.Key.Column3,
                           Catalogue_TrainingTypeKey = (System.Int32?)g.Key.Catalogue_TrainingTypeKey,
                           TenLHDT = g.Key.Column4,
                           Catalogue_StatusKey = (System.Int32?)g.Key.Catalogue_StatusKey,
                           TenTinhTrang = g.Key.Column5,
                           TongSoSV = (Int64?)g.Count()
                       };

            if (_iDep > 0)
            {
               view =view.Where(p => p.Catalogue_DepartmentKey == int.Parse(_iDep.ToString()));
            }
            if (_iMaj > 0)
            {
               view =view.Where(p => p.Catalogue_MajorKey == int.Parse(_iMaj.ToString()));
            }
            if (_iTrai > 0)
            {
               view =view.Where(p => p.Catalogue_TrainingTypeKey == int.Parse(_iTrai.ToString()));
            }
            if (_iCou > 0)
            {
               view =view.Where(p => p.Catalogue_CourseKey == int.Parse(_iCou.ToString()));
            }
            if (_ilevel > 0)
            {
               view =view.Where(p => p.Catalogue_LevelKey == int.Parse(_ilevel.ToString()));
            }
            view = view.OrderBy(p => p.Catalogue_CourseKey);
            return view.Distinct();
        }


    }
}
